package nl.maarten.docenthelp.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import nl.maarten.docenthelp.entity.Category;
import nl.maarten.docenthelp.entity.Problem;
import nl.maarten.docenthelp.entity.User;
import nl.maarten.docenthelp.entity.UserType;

public class Database 
{
	private static Database instanceDatabase;
	
	private Connection con = null;
	private PreparedStatement ps = null;
	private Statement statement = null;
	private ResultSet rs = null;
	
	public static Database getInstance()
	{
		if( instanceDatabase == null)
		{
			instanceDatabase = new Database();
		}
		
		return instanceDatabase;
	}
	
	private Database()
	{
		
	}
	
	public void connect()
	{
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost/Docenthelp?user=root&password=");
			
		} catch (Exception e) 
		{
			e.printStackTrace();
		}
	}
	
	public ArrayList<User> getUsers()
	{
		ArrayList<User> users = new ArrayList<User>();
		
		connect();
		
		try 
		{
			statement = con.createStatement();
			rs = statement.executeQuery("SELECT * FROM user" );

			while(rs.next())
			{
				int id = rs.getInt(1);
				String name = rs.getString(2);
				UserType type = UserType.fromId( rs.getInt(4));
				users.add(new User(id, name, type));
			}
		}
		catch( SQLException e)
		{
			e.printStackTrace();
		}
		
		close();
		
		return users;
	}
	
	public ArrayList<Problem> getProblems()
	{
		ArrayList<Problem> problems = new ArrayList<Problem>();
		
		connect();
		
		try
		{
			statement = con.createStatement();
			rs = statement.executeQuery("SELECT		user.username,"
									+ "				user.userid,"
									+ "				user.type,"
									+ "				category.id AS categoryid,"
									+ "				category.name,"
									+ "				category.abbreviation,"
									+ "				problem.problemID,"
									+ "				problem.corrected"
									+ "	FROM 		problem"
									+ "	JOIN		user"
									+ "	ON			problem.user_id = user.userid"
									+ "	JOIN 		category"
									+ "	ON 			problem.category_id = category.id" );

			while(rs.next())
			{
				User user = new User( rs.getInt( 2 ), rs.getString( 1 ), UserType.fromId( rs.getInt( 3 ) ) );
				Category category = new Category( rs.getInt( 4 ), rs.getString( 5 ), rs.getString( 6 ) );
				Problem problem = new Problem( rs.getInt( 7 ), user, category, rs.getBoolean( 8 ) );
				
				problems.add( problem );
			}
		}
		catch( SQLException e )
		{
			e.printStackTrace();
		}
		
		close();
		
		return problems;
	}
	
	public void updateProblem( Problem problem )
	{
		connect();
		
		try
		{
			ps = con.prepareStatement( " UPDATE		problem"
									+  " SET		corrected = ?,"
									+  "			user_id = ?,"
									+  "			category_id = ?"
									+  " WHERE		problemID = ?");
			ps.setBoolean(1, problem.isCorrected());
			ps.setInt(2, problem.getUser().getUid());
			ps.setInt(3, problem.getCategory().getId());
			ps.setInt(4, problem.getID());
			ps.executeUpdate();
			
			problem.updated();
		}
		catch( SQLException e )
		{
			e.printStackTrace();
		}
		
		close();
	}
	
	public User checkLogin(String username, String password)
	{
		connect();
		
		try 
		{
			statement = con.createStatement();
			rs = statement.executeQuery("SELECT password FROM user WHERE username = \"" + username + "\"" );
			String pass = "";
			while(rs.next())
			{
				pass = rs.getString(1);
			}
			
			if (pass != "")
			{
				rs = statement.executeQuery("SELECT * FROM user WHERE username = \"" + username + "\" AND password = \"" + password + "\"" );
				while(rs.next())
				{
					int uid = rs.getInt(1);
					UserType type = UserType.fromId(rs.getInt(4));
					return new User(uid, username, type);
				}
				
				JOptionPane.showMessageDialog(null, "password incorrect!");
				
			}
			else
			{
				JOptionPane.showMessageDialog(null, "User " + username + " niet gevonden!");
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		
		close();
		
		return null; 
	}
	
	public ArrayList<Category> getCategories()
	{
		ArrayList<Category> categories = new ArrayList<Category>();
		
		connect();
		
		try
		{
			statement = con.createStatement();
			rs = statement.executeQuery("SELECT * FROM category" );
			
			while(rs.next())
			{
				int id = rs.getInt(1);
				String name = rs.getString(2);
				String abb = rs.getString(3);
				
				categories.add(new Category(id, name, abb));
			}	
		}
		catch( SQLException e )
		{
			e.printStackTrace();
		}
		
		close();
		
		return categories;
	}
	
	public void close()
	{ 
		if (con != null)
		{ 
			try 
			{
				con.close();
			} catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
	}
}
